// 商品接口

const express = require("express");
const router = express.Router();

// MySql 链接池
const pool = require('../pool');


// 商品列表，req接收 page 和 pagesize
router.get("/list", (req, res, next) => {
    if(!req.query.page) {
        req.query.page = 1;
    }
    if(!req.query.pagesize) {
        req.query.pagesize = 5;
    }

    let page = req.query.pagesize * (req.query.page - 1);
    let pagesize = Number(req.query.pagesize);

    let sql = 'select * from product limit ?, ?';
    pool.query(sql, [page, pagesize], (err, data) => {
        if(err) {
            return next(err);
        } else {
            pool.query("select count(*) count from product", (err, r) => {
                if(err) return next(err);
                else {
                    res.send({code: 200, data, msg: "已查询", page: Number(req.query.page), pagesize, total: r[0].count});
                }
            });
        }
    });
});

// 商品分类列表，接收参数为 cid(类别) page 和 pagesize
router.get("/list/classify", (req, res, next) => {
    if(!req.query.page) {
        req.query.page = 1;
    }
    if(!req.query.pagesize) {
        req.query.pagesize = 5;
    }

    let { cid, page, pagesize } = req.query;

    page = pagesize * (page - 1);
    pagesize = Number(pagesize);
    
    let sql = "select * from product where cid=? limit ?, ?";
    pool.query(sql, [cid, page, pagesize], (err, data) => {
        if(err) {
            return next(err);
        } else {
            pool.query("select count(*) count from product where cid = ?", [cid], (err, r) => {
                if(err) return next(err);
                else {
                    res.send({code: 200, data, msg: "已查询", page: Number(req.query.page), pagesize, total: r[0].count});
                }
            });
        }
    })
});

// 新增商品，接收参数 { cid, title, color, price, cover, allCount, pic, detail, modul, shelves }
router.post("/add", (req, res, next) => {
    let { cid, title, color, price, cover, allCount, pic, detail, modul, shelves } = req.body;

    pool.query("select * from classify where cid=?", [cid], (err, r) => {
        if(err) {
            return next(err);
        } else {
            // 预备一个空的时间值
            let d = null;
            // 检查是否上架
            if(shelves == true) {
                d = new Date().getTime();
                d += "";
            }

            let arr = [ cid, title, color, price, cover, r[0].type, allCount, pic, detail, modul, d, shelves ];
            let sql = `insert into 
                product(cid, title, color, price, cover, type, allCount, pic, detail, modul, birth_date, shelves)
                values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            `;
            pool.query(sql, arr, (err, data) => {
                if(err) {
                    return next(err);
                } else {
                    res.send({code: 200, msg: "商品添加成功"});
                }
            });
        }
    });
});

// 删除商品，接收参数 { pid }
router.post("/delete", (req, res, next) => {
    let { pid } = req.body;

    let sql = "delete from product where pid=?";
    pool.query(sql, [pid], (err, data) => {
        if(err) {
            return next(err);
        } else {
            if(data.affectedRows) {
                res.send({code: 200, msg: "删除成功"});
            } else {
                res.send({code: 400, msg: "删除失败"});
            }
        }
    });
});

// 修改商品，接收参数 { pid, cid, title, color, price, cover, allCount, pic, detail, modul, shelves }
router.post("/modify", (req, res, next) => {
    let { pid, cid, title, color, price, cover, allCount, pic, detail, modul, shelves } = req.body;

    let multitable = `select a.type, b.birth_date
        from classify a, product b
        where a.cid = ? and b.pid = ?
    `;
    pool.query(multitable, [cid, pid], (err, r) => {
        if(err) {
            return next(err);
        } else {
            // 预备一个空的时间值
            let d = null;
            let arr = [cid, title, color, price, cover, r[0].type, allCount, pic, detail, modul, shelves, pid];

            // 检查时间戳
            if(r[0].birth_date == null) {
                // 是否上架
                if(shelves == true) {
                    d = new Date().getTime();
                    d += "";
                    // 插入数组
                    arr.splice(10, 0, d);
                }
            }

            let sql = `update product set
                cid = ?,
                title = ?,
                color = ?,
                price = ?,
                cover = ?,
                type = ?,
                allCount = ?,
                pic = ?,
                detail = ?,
                modul = ?,
                ${
                    // 如果 birth_date 为空 并且 shelves 为真 则给于上架时间
                    r[0].birth_date == null && shelves == true ? "birth_date = ?," : ""
                }
                shelves = ?
                where pid = ?
            `;

            pool.query(sql, arr, (err, data) => {
                if(err) {
                    return next(err);
                } else {
                    res.send({code: 200, msg: "修改成功"});
                }
            })
        }
    });
});

// 商品详情，接收参数 { pid }
router.get("/details", (req, res, next) => {
    let { pid } =  req.query;

    let sql = "select * from product where pid = ?";
    pool.query(sql, [pid], (err, r) => {
        if(err) {
            return next(err);
        } else {
            if(r.length == 0) {
                res.send({code: 400, msg: "未查询到"});
            } else {
                res.send({code: 200, data: r[0], msg: "已查询"});
            }
        }
    });
});

// 通过搜索来查询商品，接收参数 { title }
router.get("/search", (req, res, next) => {
    if(!req.query.page) {
        req.query.page = 1;
    }
    if(!req.query.pagesize) {
        req.query.pagesize = 5;
    }
    if(req.query.title != "") {
        let title = req.query.title;
        let page = req.query.pagesize * (req.query.page - 1);
        let pagesize = Number(req.query.pagesize);
    
        pool.query("select * from product where title like ? limit ?, ?", [`%${title}%`, page, pagesize], (err, data) => {
            if(err) {
                return next(err);
            } else {
                if(data.length == 0) {
                    res.send({code: 400, msg: "未搜索到"});                    
                } else {
                    pool.query("select count(*) count from product where title like ?", [`%${title}%`], (err, r) => {
                        if(err) return next(err);
                        else {
                            res.send({code: 200, data, msg: "已查询", page: Number(req.query.page), pagesize, total: r[0].count});
                        }
                    });
                }
            }
        });
    } else {
        res.send({code: 400, msg: "title 的值是未知的"});
    }
});

// 查询商品类型
router.get("/category", (req, res, next) => {
    pool.query("select * from classify", (err, data) => {
        if(err) return next(err);
        else {
            res.send({code: 200, data, msg: "已查询"});
        }
    });
});

// 单独修改商品是否上架 { pid, shelves }
router.post("/shelves", (req, res, next) => {
    let { pid, shelves } = req.body;
    let sql = "update product set shelves = ? where pid = ?"

    pool.query(sql, [shelves, pid], (err, data) => {
        if(err) next(err);
        else {
            res.send({code: 200, msg: shelves == 1 ? "已上架" : "已下架"});
        }
    })
});

module.exports = router;